<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
        <meta name="language" content="sk" /> 
        
<style type="text/css">
#report
{
font-family:font-family: Verdana, Arial, Helvetica, sans-serif;
width:100%;
border-collapse:collapse;
}
td, th 
{
font-size:1em;
border:1px solid #98bf21;
padding:3px 7px 2px 7px;
}
th 
{
font-size:16px;
text-align:left;
padding-top:5px;
padding-bottom:4px;
background-color:#A7C942;
color:#ffffff;
}
tr.alt td 
{
color:#000000;
background-color:#EAF2D3;
}
</style>
</head>
<body>
<h2>Report #1: Status študentov a používateľských účtov softvérových učební</h2>

<?php
	if (!isset($_GET["mode"])) {
		echo '<a href="report_1.php?mode=0">VŠETKO</a><br/><br/>';
		echo '<a href="report_1.php?mode=1">ŠTUDENT:štúdium ukoncené vylúcením && SOFTVÉROVÉKONTO:aktívny</a><br/>';
		echo '<a href="report_1.php?mode=2">ŠTUDENT:štúdium ukoncené vylúcením && SOFTVÉROVÉKONTO:zablokovaný a nezrušený</a><br/>';
		echo '<a href="report_1.php?mode=3">ŠTUDENT:riadne denné štúdium && SOFTVÉROVÉKONTO:zablokovaný</a><br/>';
		echo '<a href="report_1.php?mode=4">ŠTUDENT:riadne denné štúdium && SOFTVÉROVÉKONTO:zrušený</a><br/>';
		echo '<a href="report_1.php?mode=5">ŠTUDENT:úspešne absolvované štúdium && SOFTVÉROVÉKONTO:aktívny</a><br/>';
		echo '<a href="report_1.php?mode=6">ŠTUDENT:úspešne absolvované štúdium && SOFTVÉROVÉKONTO:zablokovaný a nezrušený</a><br/>';
		echo '<a href="report_1.php?mode=7">ŠTUDENT:prerušené štúdium && SOFTVÉROVÉKONTO:aktívny</a><br/>';
		echo '<a href="report_1.php?mode=8">ŠTUDENT:prerušené štúdium štúdium && SOFTVÉROVÉKONTO:zrušený</a><br/>';
		echo '<a href="report_1.php?mode=9">ŠTUDENT:all && SOFTVÉROVÉKONTO:null</a><br/>';
	}
	if (isset($_GET["mode"])) {
		$connection = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$connection) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
			exit(0);
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("dwh", $connection);

		if ($_GET["mode"] == 0) {		// VSETKO
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
					LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
					LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
					LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey;
			";
		}
		if ($_GET["mode"] == 1) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"štúdium ukoncené vylúcením\"
					AND
					ua.Locked = 0
					AND
					ua.Deleted = 0
				;
			";
		}
		if ($_GET["mode"] == 2) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"štúdium ukoncené vylúcením\"
					AND
					ua.Locked = 1
					AND
					ua.Deleted = 0
				;
			";
		}
		if ($_GET["mode"] == 3) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"riadne denné štúdium\"
					AND
					ua.Locked = 1
				;
			";
		}
		if ($_GET["mode"] == 4) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"riadne denné štúdium\"
					AND
					ua.Deleted = 1
				;
			";
		}
		if ($_GET["mode"] == 5) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"úspešne absolvované štúdium\"
					AND
					ua.Locked = 0
					AND
					ua.Deleted = 0
				;
			";
		}
		if ($_GET["mode"] == 6) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"úspešne absolvované štúdium\"
					AND
					ua.Locked = 1
					AND
					ua.Deleted = 0
				;
			";
		}
		if ($_GET["mode"] == 7) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"prerušené štúdium\"
					AND
					ua.Locked = 0
					AND
					ua.Deleted = 0
				;
			";
		}
		if ($_GET["mode"] == 8) {
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE
					ss.Name = \"prerušené štúdium\"
					AND
					ua.Deleted = 1
				;
			";
		}
		if ($_GET["mode"] == 9) {		// AK NEMAJU USERACCOUNT
			$queryString = "
				SELECT
					std.StudentKey,
					std.AIS_ID,
					std.FirstName,
					std.LastName,
					fos.Name,
					std.Grade,
					ss.Name,
					ua.Username,
					ua.Domain,
					ua.Locked,
					ua.Deleted
				FROM dwh.student AS std
				LEFT JOIN dwh.studystatus ss ON ss.StudyStatusKey = std.StudyStatusKey
				LEFT JOIN dwh.fieldofstudy fos ON fos.FieldOfStudyKey = std.FieldOfStudyKey
				LEFT JOIN dwh.useraccount ua ON ua.StudentKey = std.StudentKey
				WHERE ua.Username IS NULL;
			";
		}

		$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
		echo '<table border="1">' . "\n";
		echo '<tr>';
		echo '<th>AIS ID</th>';
		echo '<th>Meno</th>';
		echo '<th>Priezvisko</th>';
		echo '<th>Študijný odbor</th>';
		echo '<th>Ročník</th>';
		echo '<th>Štatút</th>';
		echo '</tr>' . "\n";
		while($row = mysql_fetch_array($queryAnswer) ) {
			
			echo '<tr>';
			echo '<td>' . $row[1] . '</td>';
			echo '<td>' . $row[2] . '</td>';
			echo '<td>' . $row[3] . '</td>';
			echo '<td>' . $row[4] . '</td>';
			echo '<td>' . $row[5] . '</td>';
			echo '<td>' . $row[6] . '</td>';
			echo '</tr>' . "\n";

			echo '<tr>';
			echo '<td colspan="6"><b>Softvérové konto</b><br/>';
			echo 'Prihlasovacie meno: '.$row[7].'<br/>';
			echo 'Doména: '.$row[8].'<br/>';
			if ($row[7] != null) {
				if ($row[9] == 0 && $row[10] == 0) $stav = "aktívny";
				if ($row[9] == 0 && $row[10] == 1) $stav = "zrušený";
				if ($row[9] == 1 && $row[10] == 0) $stav = "zablokovaný";
				if ($row[9] == 1 && $row[10] == 1) $stav = "zrušený aj zablokovaný";
				if ($row[9] == 0) $row[9] = "no"; else $row[9] = "yes";
				if ($row[10]== 0) $row[10] = "no"; else $row[10] = "yes";
			} else $stav = "<b>POUZIVATEL NEMA SOFTVEROVE KONTO !!!</b>";
			echo 'Locked: ' .  $row[9] . '<br/>';
			echo 'Deleted: ' . $row[10] . '<br/>';
			echo 'Stav: ' . $stav . '<br/>';
			echo '</td>';
			echo '</tr>' . "\n";
			
		}
		echo '</table>';
		
		mysql_close($connection);
	}
?>


</body>
</html>